﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Sistema_de_Taller.SDT_DAL.Utilities
{
    public class Client_DAL
    {

       /* public void ClientInsert(Common_Files.Definitions.Client Client)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand(@"insert into Client (Client_Name, Client_Social, Client_State, Client_Residence, Client_Email, Client_DateUp, Client_Country, Client_Province, Client_City, Client_PostCode) 
                                                                values ('" + Client.Client_Name + "', '" + Client.Client_Social + "','" + Client.Client_State + "','" + Client.Client_Residence + "', '" + Client.Client_Email + "', '" + Client.Client_DateUp.ToShortDateString() + "', '" + Client.Client_Country + "', '" + Client.Client_Province + "', '" + Client.Client_City + "', '" + Client.Client_PostCode + "')", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);
        }*/

        public UInt32 ClientInsert(Common_Files.Definitions.Client Client)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand(@"insert into Client (Client_Name, Client_Social, Client_State, Client_Residence, Client_Email, Client_DateUp, Client_Country, Client_Province, Client_City, Client_PostCode) 
                                                                values ('" + Client.Client_Name + "', '" + Client.Client_Social + "','" + Client.Client_State + "','" + Client.Client_Residence + "', '" + Client.Client_Email + "', '" + Client.Client_DateUp.ToShortDateString() + "', '" + Client.Client_Country + "', '" + Client.Client_Province + "', '" + Client.Client_City + "', '" + Client.Client_PostCode + "'); SET @ID=SCOPE_IDENTITY()", Connection);
            //Obtengo el ID del Cliente agregado
            SqlParameter par = new SqlParameter();
            par.ParameterName = "@ID";
            par.Size = 4;
            par.Direction = ParameterDirection.Output;
            Command.Parameters.Add(par);
            Command.ExecuteNonQuery();
            Connection.Close();
            return(Convert.ToUInt32(par.Value));
        }

        public DataSet ClientLoad()
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlDataAdapter Command = new SqlDataAdapter(@"Select Client_Name as 'Cliente', Client_Social as 'Razón Social', Client_State as 'Estado', Client_Residence as 'Domicilio', Client_Email as 'Mail', Client_DateUp as 'Fecha Alta', Client_Country as 'País', Client_Province as 'Provincia', Client_City as 'Ciudad', Client_PostCode as 'Código Postal' from client", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public DataSet ClientLoadID(UInt32 ClientID)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlDataAdapter Command = new SqlDataAdapter(@"Select Client_Name as 'Cliente', Client_Social as 'Razón Social', Client_State as 'Estado', Client_Residence as 'Domicilio', Client_Email as 'Mail', Client_DateUp as 'Fecha Alta', Client_Country as 'País', Client_Province as 'Provincia', Client_City as 'Ciudad', Client_PostCode as 'Código Postal' from client where Client_ID = '" + ClientID + "'", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public UInt32 ClientUpdate(Common_Files.Definitions.Client Client)
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand("update Client set Client_Name= '" + Client.Client_Name + "' , Client_State = '" + Client.Client_State + "', Client_Residence = '" + Client.Client_Residence + "', Client_Email = '" + Client.Client_Email + "' , Client_DateUp = '" + Client.Client_DateUp.ToShortDateString() + "' ,"
                                                + "Client_Country = '" + Client.Client_Country + "', Client_Province = '" + Client.Client_Province + "', Client_Social = '" + Client.Client_Social + "', Client_City = '" + Client.Client_City + "', Client_PostCode = '" + Client.Client_PostCode + "' where Client_ID = '" + Client.Client_ID + "'", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);
            return (Client.Client_ID);
        }    
        
        public void ClientDelete()
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
          //  SqlCommand Command = new SqlCommand("delete from Client where mov_codigo = '" + DgvDatos.SelectedRows[0].Cells[0].Value.ToString() + "'", Connection);
          //  CloseConecction(Connection, Command);
        }

        public DataSet ClientSearch(String Parameter, String Column)
        {
            String query = string.Format("Select Client_Name as 'Cliente', Client_State as 'Estado', Client_Residence as 'Domicilio', Client_Email as 'Mail', Client_DateUp as 'Fecha Alta', Client_Country as 'País', Client_Province as 'Provincia', Client_City as 'Ciudad', Client_PostCode as 'Código Postal' from client where {0} like '%" + Parameter + "%'", Column);
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlDataAdapter Command = new SqlDataAdapter(query, Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }
    }
}